﻿Imports System.IO

Public Class ClassStock
    Public Shared Function addProduct(ByVal code As Integer, ByVal name As String, ByVal alert As Integer, ByVal photo As Image) As Boolean
        Dim sql1 = "insert into tbl_item(item_code,item_name,alert,active,photo) value (@item_code,@item_name,@alert,1,@photo)"
        'Dim sql3 = "insert into tbl_stock value (@item_id,@exchange,@qty,@oldprice,@newprice,@makeup,@sellout)"
        'Dim sql2 = "SELECT Max(item_id) FROM tbl_item"
        'Dim sql4 = "select percentage from tbl_makeupprice"
        'Dim sql5 = "select currency_rate from tbl_exchange_rate_detail where exchange_rate_detail_id=@exid"
        Try
            

            'Cmd.Begin()
            Cmd.Parameters("@item_code") = code
            Cmd.Parameters("@item_name") = name
            Cmd.Parameters("@alert") = alert

            Dim MemStream As New MemoryStream
            Dim DataPic_Update As Byte()
            photo.Save(MemStream, Imaging.ImageFormat.Jpeg)
            DataPic_Update = MemStream.GetBuffer()
            MemStream.Read(DataPic_Update, 0, MemStream.Length)
            Cmd.Parameters("@photo") = DataPic_Update

            Cmd.ExecuteNonQuery(sql1)

            'Dim id = Cmd.ExecuteScalar(sql2)
            'Dim percent = Cmd.ExecuteScalar(sql4)
            'Cmd.Parameters("@exid") = exid
            'Dim exchange = Cmd.ExecuteScalar(sql5)

            'Dim makeup = ((price / exchange) * percent) + (shipping / qty)

            'Cmd.Parameters("@item_id") = id
            'Cmd.Parameters("@exchange") = exchange
            'Cmd.Parameters("@qty") = qty
            'Cmd.Parameters("@oldprice") = price
            'Cmd.Parameters("@newprice") = price
            'Cmd.Parameters("@makeup") = makeup
            'Cmd.Parameters("@sellout") = makeup
            'Cmd.ExecuteNonQuery(sql3)
            myMsgbox.show("Add new item Sucess!!!", "New Item")
            'Cmd.Commit()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
            Cmd.Rollback()
            Return False
        End Try
    End Function

    '(ByVal name As String, ByVal code As Integer, ByVal alert As Integer, ByVal photo As Bitmap, ByVal qty As Integer, ByVal price As Double, ByVal exid As Integer, ByVal shipping As Double)

    Public Shared Function LoadProduct(ByVal text As String) As DataTable
        Dim sql = "select s.makeup_price,s.sell_out,s.qty,CONCAT(s.new_price , e.currency_name) as new_price,concat(s.old_price, (select currency_name from tbl_exchange_rate_detail where exchange_rate_detail_id=s.exchange_rate_detail_id_old)) as old_price,i.item_name,i.item_code,i.alert from tbl_stock s right join tbl_item i on s.item_id=i.item_id left join tbl_exchange_rate_detail e on e.exchange_rate_detail_id=s.exchange_rate_detail_id where UPPER(i.item_name) like UPPER(@search) and i.active=true order by i.item_id"

        Try
            Dim dt As New DataTable
            Cmd.Parameters("@search") = "%" & text & "%"
            dt = Cmd.ExecuteDataTable(sql)
            Return dt
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
End Class
